﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using TaxBusiness.Model;

namespace TaxBusiness.DAL
{
    public class MenuDB
    {
        private string _insert = "INSERT INTO [MenuData] " +
                                      "([Name]" +
                                      ",[WebIcon]" +
                                      ",[Url]" +
                                      ",[ParentID]" +
                                      ",[UserAuth])" +
                                 " VALUES " +
                                       "(@Name" +
                                       ",@WebIcon" +
                                       ",@Url" +
                                       ",@ParentID" +
                                       ",@UserAuth)";

        private string _update = "UPDATE [MenuData]" +
                                   "SET [Name] = @Name" +
                                      ",[WebIcon] = @WebIcon" +
                                      ",[Url] = @Url" +
                                      ",[ParentId] = @ParentID" +
                                      ",[UserAuth] = @UserAuth" +
                                 "WHERE ID=@ID";
       
        private string _delete = "DELETE FROM [MenuData] WHERE ID=@ID";

        private string _select = "SELECT [MenuData].* FROM [MenuData] inner join [UsersMenu] on menudata.id=usersmenu.menuid and UserID=@UserID and ParentID=@ParentID";

        private string _selectAll = "select * from [MenuData] order by ID";

        public int Insert(Model.MenuData menudata)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_insert, menudata);
            }
        }

        public int Update(Model.MenuData menudata)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_update, menudata);
            }
        }

        public int Delete(int id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_delete, new { ID = id });
            }
        }


        public List<Model.MenuData> GetList(int parentid, int userid)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<Model.MenuData>(_select, new { ParentID = parentid, UserID = userid }).ToList();
            }
        }

        public List<Model.MenuData> GetAll()
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<Model.MenuData>(_selectAll).ToList();
            }
        }
    }
}
